const db = require("../config/db");

class Store {
  static async getAll() {
    const [rows] = await db.query(
      "SELECT * FROM stores WHERE status = 1 ORDER BY id ASC"
    );
    return rows;
  }

  static async getById(id) {
    const [rows] = await db.query("SELECT * FROM stores WHERE id = ?", [id]);
    return rows[0];
  }

  static async getNearbyStores(latitude, longitude, limit = 3) {
    const [rows] = await db.query(
      `
            SELECT 
                *,
                (6371 * acos(cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?)) + sin(radians(?)) * sin(radians(latitude)))) AS distance
            FROM stores
            WHERE status = 1
            ORDER BY distance
            LIMIT ?
        `,
      [latitude, longitude, latitude, limit]
    );
    return rows;
  }

  static async create(storeData) {
    const {
      name,
      address,
      phone,
      latitude,
      longitude,
      business_hours,
      description,
      image_url,
      status = 1,
    } = storeData;
    const [result] = await db.query(
      "INSERT INTO stores (name, address, phone, latitude, longitude, business_hours, description, image_url, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        name,
        address,
        phone,
        latitude,
        longitude,
        business_hours,
        description,
        image_url,
        status,
      ]
    );
    return result.insertId;
  }

  static async update(id, storeData) {
    const {
      name,
      address,
      phone,
      latitude,
      longitude,
      business_hours,
      description,
      image_url,
      status,
    } = storeData;
    const [result] = await db.query(
      "UPDATE stores SET name = ?, address = ?, phone = ?, latitude = ?, longitude = ?, business_hours = ?, description = ?, image_url = ?, status = ? WHERE id = ?",
      [
        name,
        address,
        phone,
        latitude,
        longitude,
        business_hours,
        description,
        image_url,
        status,
        id,
      ]
    );
    return result.affectedRows > 0;
  }

  static async delete(id) {
    const [result] = await db.query("DELETE FROM stores WHERE id = ?", [id]);
    return result.affectedRows > 0;
  }

  static async getByPage(offset, limit) {
    const [rows] = await db.query(
      "SELECT * FROM stores WHERE status = 1 ORDER BY id ASC LIMIT ?, ?",
      [offset, limit]
    );
    return rows;
  }

  static async getTotalCount() {
    const [rows] = await db.query(
      "SELECT COUNT(*) as total FROM stores WHERE status = 1"
    );
    return rows[0].total;
  }

  static async searchByName(keyword, offset, limit) {
    const [rows] = await db.query(
      "SELECT * FROM stores WHERE name LIKE ? AND status = 1 ORDER BY id ASC LIMIT ?, ?",
      [`%${keyword}%`, offset, limit]
    );
    return rows;
  }

  static async getSearchCount(keyword) {
    const [rows] = await db.query(
      "SELECT COUNT(*) as total FROM stores WHERE name LIKE ? AND status = 1",
      [`%${keyword}%`]
    );
    return rows[0].total;
  }
}

module.exports = Store;
